package com.runda.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;

import com.bbjob.dto.Result;
import com.bbjob.mapper.SysOrganizationMapper;
import com.bbjob.mapper.SysOrganizationStuMapper;
import com.bbjob.model.SysOrganization;
import com.bbjob.model.SysOrganizationStu;
import com.bbjob.util.service.impl.BaseService;
import com.github.pagehelper.PageHelper;
import com.runda.query.QueryExaminee;
import com.runda.service.IExamineeService;

@Service
public class ExamineeServiceImpl extends BaseService<SysOrganizationStu> implements IExamineeService {
	
	@Value("${delivery.file.path}")
	private String deliveryFileDir;
	
	@Resource
	private SysOrganizationMapper sysOrganizationMapper;
	
	@Resource
	private SysOrganizationStuMapper sysOrganizationStuMapper;
	
	@Override
	public Map<String, Object> importExaminee(MultipartFile multipartFile,
			HttpServletRequest request) throws Exception {
		
		request.setCharacterEncoding("UTF-8");
		
		Map<String, Object> returnMap = new HashMap<String, Object>();
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
		String uniquecode = "";
		String filename = "XXX";
		if (request.getParameter("uniquecode") != null && !"".equals(request.getParameter("uniquecode"))) {
			uniquecode = request.getParameter("uniquecode");
		} else {
			returnMap.put("code", 8106);
			returnMap.put("message", "学校唯一编码为空");
			returnMap.put("data", null);
			return returnMap;
		}
		if (request.getParameter("filename") != null && !"".equals(request.getParameter("filename"))) {
			filename = request.getParameter("filename");
		}
		if (!filename.endsWith("xls") && !filename.endsWith("xlsx")) {
			returnMap.put("code", 8106);
			returnMap.put("message", "不是Excel文件");
			returnMap.put("data", null);
			return returnMap;
		}
		
		String saveDirectory = deliveryFileDir + uniquecode + "/" + sdf.format(new Date()) + "/" + filename;
		
		InputStream is = null;
		
		try {
			File path = new File(saveDirectory);
			
			if (!path.getParentFile().exists()) {
				path.getParentFile().mkdirs();
				path.createNewFile();
			}
			
			multipartFile.transferTo(path);
			
			// 文件上传完毕，准备解析入库
			is = new FileInputStream(path);
			Workbook wb = null;
			if (excelVersion(path.getName())) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = new HSSFWorkbook(is);
			}
			
			Result result = analysisExcel(wb, uniquecode);
			
			returnMap.put("code", 200);
			returnMap.put("message", result.getMessage());
			returnMap.put("data", null);
			
			return returnMap;
		} catch(Exception e) {
			e.printStackTrace();
			returnMap.put("code", 8106);
			returnMap.put("message", "文件上传考试中心失败");
			returnMap.put("data", null);
			return returnMap;
		} finally {
			if (is != null) {
				is.close();
			}
		}
	}

	private Result analysisExcel(Workbook wb, String uniquecode) {
		
		Example organExample = new Example(SysOrganization.class);
		organExample.createCriteria().andEqualTo("uniqueCode", uniquecode);
		List<SysOrganization> organs = sysOrganizationMapper.selectByExample(organExample);
		
		List<String> failSno = new ArrayList<String>();
		List<String> failID = new ArrayList<String>();
		List<String> nullList = new ArrayList<String>();
		
		if (organs.size() == 0) {
			// 无法根据uniquecode查询到该学校
			return new Result(8106, "未查询到学校信息");
		}
		
		// 获取sheet页数
		int sheets = wb.getNumberOfSheets();
		
		for (int i = 0; i < sheets; i++) {
			// 得到第i个sheet
			Sheet sheet = wb.getSheetAt(i);
			// 得到当前sheet行数
			int totalRows = sheet.getPhysicalNumberOfRows();
			// 得到当前sheet的有效列数
			int totalCells = 0;
			if (totalRows > 1 && sheet.getRow(0) != null) {
				totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			
			for (int rowIndex = 1; rowIndex < totalRows; rowIndex++) {
				Row row = sheet.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				
				SysOrganizationStu stu = new SysOrganizationStu();
				stu.setOrganSchool(organs.get(0).getId());
				stu.setSchoolName(organs.get(0).getName());
				
				// 用于判断是否是空行 -- 依据是考号列是否为null
				// int flag = 0;
				
				for (int colIndex = 0; colIndex < totalCells; colIndex++) {
					Cell cell = row.getCell(colIndex);
					
					if (cell == null) {
						continue;
					}
					
					switch (colIndex) {
					case 0:
						stu.setDepartName(cell.getStringCellValue());
						break;
					case 1:
						stu.setGradeName(cell.getStringCellValue());
						break;
					case 2:
						stu.setClassName(cell.getStringCellValue());
						break;
					case 3:
						stu.setSname(cell.getStringCellValue());
						break;
					case 4:
						stu.setBirthday(cell.getDateCellValue());
						break;
					case 5:
						stu.setSexName(cell.getStringCellValue());
						break;
					case 6:
						stu.setSno(cell.getStringCellValue());
						// flag = 1;
						break;
					case 7:
						stu.setIdentityNumber(cell.getStringCellValue());
						break;
					}
				}
				
				// 考号列为null 空行， 跳过
/*				if (flag == 0) {
					continue;
				}*/
				
				// 查询是当前学校下是否存在系名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganSchool()).andEqualTo("name", stu.getDepartName());
				List<SysOrganization> departs = sysOrganizationMapper.selectByExample(organExample);
				if (departs.size() == 0) {
					SysOrganization depart = new SysOrganization();
					depart.setPid(stu.getOrganSchool());
					depart.setName(stu.getDepartName());
					depart.setLevel(2);
					depart.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(depart);
					stu.setOrganDepart(depart.getId());
				} else {
					stu.setOrganDepart(departs.get(0).getId());
				}
				
				// 查询当前系下是否存在年级名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganDepart()).andEqualTo("name", stu.getGradeName());
				List<SysOrganization> grades = sysOrganizationMapper.selectByExample(organExample);
				if (grades.size() == 0) {
					SysOrganization grade = new SysOrganization();
					grade.setPid(stu.getOrganDepart());
					grade.setName(stu.getGradeName());
					grade.setLevel(3);
					grade.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(grade);
					stu.setOrganGrade(grade.getId());
				} else {
					stu.setOrganGrade(grades.get(0).getId());
				}
				
				// 查询当前年级下是否存在班级名称，不存在则新建
				organExample.clear();
				organExample.createCriteria().andEqualTo("pid", stu.getOrganGrade()).andEqualTo("name", stu.getClassName());
				List<SysOrganization> classes = sysOrganizationMapper.selectByExample(organExample);
				if (classes.size() == 0) {
					SysOrganization clas = new SysOrganization();
					clas.setPid(stu.getOrganGrade());
					clas.setName(stu.getClassName());
					clas.setLevel(4);
					clas.setCreateTime(new Date());
					sysOrganizationMapper.insertSelective(clas);
					stu.setOrganClass(clas.getId());
				} else {
					stu.setOrganClass(classes.get(0).getId());
				}
				
				if ("男".equals(stu.getSexName())) {
					stu.setSex(1);
				} else if ("女".equals(stu.getSexName())) {
					stu.setSex(2);
				} else {
					stu.setSex(0);
				}
				
				if (stu.getSno() == null || "".equals(stu.getSno())) {
					nullList.add(rowIndex + 1 + "");
					continue;
				}
				
				Example sExample = new Example(SysOrganizationStu.class);
/*				sExample.createCriteria().andEqualTo("sno", stu.getSno());
				List<SysOrganizationStu> sameStu = sysOrganizationStuMapper.selectByExample(sExample);
				if (sameStu.size() > 0) {
					stu.setId(sameStu.get(0).getId());
					sysOrganizationStuMapper.updateByPrimaryKey(stu);
					continue; // 认为是重复导入,只负责更新的信息
				}*/
				
				sExample.clear();
				sExample.createCriteria().andEqualTo("sno", stu.getSno()).andEqualTo("organSchool", stu.getOrganSchool());
				List<SysOrganizationStu> stuBySno = sysOrganizationStuMapper.selectByExample(sExample);
				if (stuBySno.size() > 0) {
					stu.setId(stuBySno.get(0).getId());
					sysOrganizationStuMapper.updateByPrimaryKey(stu);
					failSno.add(stu.getSno());
					continue;
					// return new Result(8106, "录入失败，该学号已注册");
				}
				
				if (stu.getIdentityNumber() != null && !"".equals(stu.getIdentityNumber())) {
					sExample.clear();
					sExample.createCriteria().andEqualTo("identityNumber", stu.getIdentityNumber());
					List<SysOrganizationStu> stuByID = sysOrganizationStuMapper.selectByExample(sExample);
					if (stuByID.size() > 0) {
						failID.add(stu.getSno());
						continue;
						// return new Result(8106, "录入失败，该身份证号已注册");
					}
				}
				
				
				// 信息处理完准备存库
				Example stuExample = new Example(SysOrganizationStu.class);
				stuExample.createCriteria().andEqualTo("sno", stu.getSno()).andEqualTo("organSchool", stu.getOrganSchool());
				List<SysOrganizationStu> stus = sysOrganizationStuMapper.selectByExample(stuExample);
				if (stus.size() > 0) {
					stu.setId(stus.get(0).getId());
					sysOrganizationStuMapper.updateByPrimaryKey(stu);
				} else {
					sysOrganizationStuMapper.insertSelective(stu);
				}
			}
		}
		
		String msgTip = "";
		
		if (failID.size() == 0 && failSno.size() == 0 && nullList.size() == 0) {
			msgTip = "学生信息导入成功";
		} else {
			if (failID.size() > 0) {
				msgTip += "学号：" + failID.toString() + "使用的身份证号已注册，已跳过保存，请修改";
				if (failSno.size() > 0) {
					msgTip += ";学号：" + failSno.toString() + "已注册，执行更新操作";
				}
				if (nullList.size() > 0) {
					msgTip += ";序号：" + nullList.toString() + "学号为空，已跳过保存，请修改";
				}
				
			} else {
				if (failSno.size() > 0) {
					msgTip += "学号：" + failSno.toString() + "已注册，执行更新操作";
					if (nullList.size() > 0) {
						msgTip += ";序号：" + nullList.toString() + "学号为空，已跳过保存，请修改";
					}
				} else {
					msgTip += "序号：" + nullList.toString() + "学号为空，已跳过保存，请修改";
				}
			} 
		}
		
		return new Result(200, msgTip);
	}

	private boolean excelVersion(String name) {
		
		return name.matches("^.+\\.(?i)(xlsx)$");
	}

	@Override
	public List<SysOrganizationStu> selectByParams(QueryExaminee examinee) {

		if (examinee.getUniquecode() != null && !"".equals(examinee.getUniquecode())) {
			Example organExample = new Example(SysOrganization.class);
			organExample.createCriteria().andEqualTo("uniqueCode", examinee.getUniquecode());
			List<SysOrganization> organs = sysOrganizationMapper.selectByExample(organExample);
			
			if (organs.size() > 0) {
				examinee.setOrganSchool(organs.get(0).getId());
			}
		}
		
		PageHelper.startPage(examinee.getPageNum(), examinee.getPageSize());
		
		Example example = new Example(SysOrganizationStu.class);
		Criteria c = example.createCriteria();
		

		if (examinee.getSno() != null && !"".equals(examinee.getSno())) {
			c.andLike("sno", "%" + examinee.getSno() + "%");
		}

		if (examinee.getSname() != null && !"".equals(examinee.getSname())) {
			c.andLike("sname", "%" + examinee.getSname() + "%");
		}
		
		if (examinee.getSex() != null) {
			c.andEqualTo("sex", examinee.getSex());
		}
		
		if (examinee.getIdentityNumber() != null && !"".equals(examinee.getIdentityNumber())) {
			c.andLike("identityNumber", "%" + examinee.getIdentityNumber() + "%");
		}
		
		if (examinee.getOrganSchool() != null) {
			c.andEqualTo("organSchool", examinee.getOrganSchool());
		}
		
		if (examinee.getOrganDepart() != null) {
			c.andEqualTo("organDepart", examinee.getOrganDepart());
		}
		
		if (examinee.getOrganGrade() != null) {
			c.andEqualTo("organGrade", examinee.getOrganGrade());
		}
		
		if (examinee.getOrganClass() != null) {
			c.andEqualTo("organClass", examinee.getOrganClass());
		}
		
		List<SysOrganizationStu> list = sysOrganizationStuMapper.selectByExample(example);
		
		for (SysOrganizationStu stu : list) {
			
			SysOrganization organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganSchool());
			stu.setSchoolName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganDepart());
			stu.setDepartName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganGrade());
			stu.setGradeName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganClass());
			stu.setClassName(organ.getName());
		}
		
		return list;
	}

	@Override
	public SysOrganizationStu selectByPrimaryKey(Integer id) {
		SysOrganizationStu stu = sysOrganizationStuMapper.selectByPrimaryKey(id);
		
		if (stu != null) {
			SysOrganization organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganSchool());
			stu.setSchoolName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganDepart());
			stu.setDepartName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganGrade());
			stu.setGradeName(organ.getName());
			
			organ = sysOrganizationMapper.selectByPrimaryKey(stu.getOrganClass());
			stu.setClassName(organ.getName());
		}
		
		return stu;
	}

}
